Highlight the data on Microsoft Access Form.

This article is to enabling the searching on the textbox based on the selecting field on dropdown. This article basically demonstrates the searching criterion to highlight the string data from continues Form.

For implementation of this we need a database from where we have to find the record. So firstly we have to make table as shown in Fig 1.1.

Next is we have to create the Form with given control like combo box and textbox in header part and also bound with table filelds.Now we have to code on boths after update property. In detail part we have to take two textbox and bound with appropriate bound fileds according to requirement. And take another textbox for highlight and make it unbound. And set it to in Continues from as shown in Fig 1.2.

After bound all the control we have to view in Form view as shown in Fig 1.3.

After coding part we have to test this searching Form. Type some alphabet in search text-box. The matching fields will come with highlighted text as shown in Fig 1.4.

VBA CODE

Option Compare Database
Option Explicit
Private Const conMod = "Form_Highlight"
Private Sub Form_Load()
Me.txtSearchText = Null
Call SElECTION_AfterUpdate
Exit_Handler:
Exit Sub
End Sub
Private Sub SElECTION_AfterUpdate()
Dim ctl As Control
If Not IsNull(Me.SELECTION) Then
Set ctl = Me.SELECTION
With Me.txtSearchDisplay
ctl.Top = ctl.Top
ctl.Left = ctl.Left
End With
End If
Call txtSearchText_AfterUpdate
Exit_Handler:
Set ctl = Nothing
Exit Sub
End Sub
Private Sub txtSearchText_AfterUpdate()
Dim strField As String
Const TAGSTRING = ""
Const ENDTAG = ""
Dim SEARCHSTRING As String
Dim CONTROLSTRING As String
Const WILDCARDSTRING = "*"
If IsNull(Me.SELECTION) Or IsNull(Me.txtSearchText) Then
If Me.FilterOn Then
Me.FilterOn = False
End If
Else
strField = "[" & Me.SELECTION & "]"
SEARCHSTRING = Me.txtSearchText
Me.Filter = strField & " Like """ & WILDCARDSTRING & SEARCHSTRING & WILDCARDSTRING & """"
Me.FilterOn = True
CONTROLSTRING = "=IIf(" & strField & " Is Null, Null, " & "Replace(" & strField & ", """ & SEARCHSTRING & """, """ &TAGSTRING & SEARCHSTRING & ENDTAG & """))"
With Me.txtSearchDisplay
.ControlSource = CONTROLSTRING
.Visible = True
End With
End If
Exit_Handler:
Exit Sub
End Sub
End Sub